﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

namespace AE.Common.SimpleORM
{
    public class OrmUtil<T>
    {
        private Type _dType;
        private PropertyInfo[] properties;
        public SqlConnection Connection { get; set; }
        public OrmUtil()
        {
            _dType = typeof(T);
            properties = _dType.GetProperties();
        }
        /// <summary>
        /// 检查数据库连接状态
        /// </summary>
        /// <returns></returns>
        private bool CheckConnection()
        {
            return Connection?.State == ConnectionState.Open;
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="entity">实体数据对象</param>
        /// <returns></returns>
        public int Insert(T entity)
        {
            if (!CheckConnection()) return -1;//检查数据库连接状态
            var insert = $"insert into {_dType.Name}({string.Join(",",properties.Select(t=>t.Name))})";
            var values = properties.Select(p => p.GetValue(entity));
            var commandText = $"{insert} values('{string.Join("','", values)}')";
            var command = Connection.CreateCommand();
            command.CommandText = commandText;
            var result = command.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="entity">实体数据对象</param>
        /// <returns></returns>
        public int Update(T entity,string keyName,string keyValue)
        {
            if (!CheckConnection()) return -1;//检查数据库连接状态
            var setValues = properties.ToDictionary(p => p.Name, p => $"'{p.GetValue(entity)}'");//获取当前所有属性值存入字典中
            var setSql = string.Join(",", setValues.Select(pair => $"{pair.Key} = '{pair.Value}'"));
            var sql = $"update {_dType.Name} set {setSql} where {keyName} = '{keyValue}'";
            var command = Connection.CreateCommand();
            command.CommandText = sql;
            return command.ExecuteNonQuery();
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="keyValuePair"></param>
        /// <returns></returns>
        public int Delete(KeyValuePair<string,string> keyValuePair)
        {
            if (!CheckConnection()) return -1;//检查数据库连接状态
            var sql = $"delete from {_dType.Name} where {keyValuePair.Key} = '{keyValuePair.Value}'";
            var command = Connection.CreateCommand();
            command.CommandText = sql;
            return command.ExecuteNonQuery();
        }

        private List<T> Convert(DataTable table)
        {
            var list = new List<T>(table.Rows.Count);
            foreach (DataRow row in table.AsEnumerable())
            {
                T entity = Activator.CreateInstance<T>();
                foreach (var p in properties)
                {
                    if (!table.Columns.Contains(p.Name)) continue;//属性名在表中不存在则跳过
                    p.SetValue(entity, row[p.Name]);
                }
                list.Add(entity);
            }
            return list;
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sqlWhere">查询条件</param>
        /// <returns></returns>
        public List<T> Search(string sqlWhere)
        {
            var sql = $"select * from {_dType.Name} {sqlWhere}";
            var adapter = new SqlDataAdapter(sql, Connection);
            var set = new DataSet();
            adapter.Fill(set);
            return Convert(set.Tables[0]);
        }
    }
}
